import random
import subprocess

from analysis_core import log_response


MYSQL_CLI = f"mysql -uroot -p%s -e sql_placeholder" 
MYSQL_EXEC_LIST = [
    "select * from protein_demo where token in ('A0A4Y4LZJ8', 'A0A329ZC87');",
    "select * from protein_demo where name='A0A1F3WWV3_9PROT CRISPR-associated endonuclease Cas9';",
    #"select * from protein_demo where sequence like '%EALDAKTKVSKNQARRTARAMRRQVARHKMRRNAALRYLTNNNLLPADQVE%';",
    "select * from protein_demo where attribution like '%OX=86473%'",
]
MYSQL_RESULT_SAVE = "~/yemd/result.csv"
MYSQL_PASSWORD = "zhejianglab"


@log_response(key="CLI")
def to_cli_list(cli: str) -> list:
    return cli.split(" ")


def subprocess_run(cli: str | list, shell: bool = False, input: str = None, to_change: bool = True) -> subprocess.CompletedProcess:
    return subprocess.run(
            args=to_cli_list(cli=cli) if to_change else cli,
            stdout=subprocess.PIPE,     # 表示重定向shell命令的输出，可以为后面程序所调用
            text=True,                  # 表示将shell命令执行的stdout和stderr当成字符串（python3.8以后默认为bytes类型）
            check=True,                 # 表示开启shell命令的执行校验，当shell出任何问题时，都会抛出一个异常
            input=input,                # 表示将字节或字符串传递给子进程的标准输入(有密码交互的逻辑，并不适合这个参数)
            shell=True
            )    


def execute_sql_command(sql: str) -> dict:
    args = to_cli_list(cli=MYSQL_CLI % MYSQL_PASSWORD)
    for i, arg in enumerate(args):
        if arg == "sql_placeholder":
            args[i] = '"%s"' % sql
            break
    print(f"NEW args list: %s" % repr(args))
    try:
        '''
        1、默认使用shell命令执行，否则只会直接调用底层的系统调用
        2、另外注意：subprocess.run的命令列表并不支持重定向，因为这并不是python可以处理的

        TODO：以下问题暂时无法解决，换用pymysql执行。请查看analysis_search.py脚本
ERROR 1045 (28000): Access denied for user 'zjlab'@'localhost' (using password: NO)
subprocess.run error: CalledProcessError(1, ['mysql', '-uroot', '-pzhejianglab', '-e', '"select * from protein_demo where name=\'A0A1F3WWV3_9PROT CRISPR-associated endonuclease Cas9\';"'])
        '''
        return_code = subprocess_run(args, shell=True, to_change=False)
    except Exception as e:
        # 
        print(f"subprocess.run error: %s" % repr(e))
        return None

    print(type(return_code))
    print("return code:", return_code)
    if return_code.returncode != 0:
        print(f"returncode=%s err" % return_code.returncode)
        return None


@log_response(key="SQL")
def random_sql():
    return random.choice(MYSQL_EXEC_LIST)


def main():
    execute_sql_command(random_sql())


if __name__ == "__main__":
    main()
